Model Table GL ANALYSIS MEASURES

Represents all measures related to GL ANALYSIS.

Attributes

Attribute

Description (where applicable)

Measures (table to be generated as far as possible)

Aggregation of measures is based on SUM if not explicitly noted.

This section describes the basic measures in the cube. These are either measures defined in the core Information Source, in the model itself or in the ETL process. The columns have the following meaning:

Core Measure: A measure that has its origin in a core Information Source
Calculated Y/N: A measure specifically defined for the model, e.g. percentages etc. These measures do not derive from the Information Source.
Contains Time Intelligence Y/N: Some measures do also have additional time intelligence measures like Year To Date (YTD) Se separate section.
Definition/Note: Specifies if there is a specific logic behind a measure. More information might also be found in documentation for Information Sources.
Aggregation Type: Specifies the aggregation type of a measure.
Hidden Y/N: Specifies if a measure is hidden and there by not visible to the end user when browsing the model.
Tabular Expression: The DAX expression in the measure definition.

Measure	Calculated Y/N	Contains Time Intelligence Y/N	Definition/Note (where applicable)	Aggregation Type	Hidden Y/N	Tabular Expression
ACT	Y	N		SUM	N	SUM('GL ANALYSIS'[ACT_PERIOD_DOM])
ACT Credit	Y	N		SUM	N	SUM('GL ANALYSIS'[ACT_PERIOD_CREDIT_DOM])
ACT Debit	Y	N		SUM	N	SUM('GL ANALYSIS'[ACT_PERIOD_DEBET_DOM])
BUD	Y	N		SUM	N	SUM('GL ANALYSIS'[BUD_PERIOD_DOM])
CFC	Y	N		SUM	N	SUM('GL ANALYSIS'[CFC_PERIOD_DOM])
NFC	Y	N		SUM	N	SUM('GL ANALYSIS'[NFC_PERIOD_DOM])
ACT Credit Trans	Y	N		SUM	N	SUM('GL ANALYSIS'[ACT_PERIOD_CREDIT_CURR])
ACT Debit Trans	Y	N		SUM	N	SUM('GL ANALYSIS'[ACT_PERIOD_DEBET_CURR])
ACT Trans	Y	N		SUM	N	SUM('GL ANALYSIS'[ACT_PERIOD_CURR])
BUD Trans	Y	N		SUM	N	SUM('GL ANALYSIS'[BUD_PERIOD_CURR])
CFC Trans	Y	N		SUM	N	SUM('GL ANALYSIS'[CFC_PERIOD_CURR])
NFC Trans	Y	N		SUM	N	SUM('GL ANALYSIS'[NFC_PERIOD_CURR])
ACT OB	Y	N		SUM	N	IF(ISBLANK([ACT YTD]) , BLANK() ,([ACT YTD] - [ACT]))
BUD OB	Y	N		SUM	N	IF(ISBLANK([BUD YTD]) , BLANK() ,([BUD YTD] - [BUD]))
CFC OB	Y	N		SUM	N	IF(ISBLANK([CFC YTD]) , BLANK() ,([CFC YTD] - [CFC]))
NFC OB	Y	N		SUM	N	IF(ISBLANK([NFC YTD]) , BLANK() ,([NFC YTD] - [NFC]))
ACT OB Trans	Y	N		SUM	N	IF(ISBLANK([ACT YTD Trans]) , BLANK() ,([ACT YTD Trans] - [ACT Trans]))
BUD OB Trans	Y	N		SUM	N	IF(ISBLANK([BUD YTD Trans]) , BLANK() ,([BUD YTD Trans] - [BUD Trans]))
CFC OB Trans	Y	N		SUM	N	IF(ISBLANK([CFC YTD Trans]) , BLANK() ,([CFC YTD Trans] - [CFC Trans]))
NFC OB Trans	Y	N		SUM	N	IF(ISBLANK([NFC YTD Trans]) , BLANK() ,([NFC YTD Trans] - [NFC Trans]))
ACT PY Trans	Y	Y		SUM	N	CALCULATE([ACT Trans], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY Trans	Y	Y		SUM	N	CALCULATE([BUD Trans], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACT PY R12 Trans	Y	Y		SUM	N	CALCULATE([ACT R12 Trans], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY R12 Trans	Y	Y		SUM	N	CALCULATE([BUD R12 Trans], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACT PY YTD Trans	Y	Y		SUM	N	CALCULATE([ACT YTD Trans], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY YTD Trans	Y	Y		SUM	N	CALCULATE([BUD YTD Trans], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACT R12 Trans	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE( [ACT Trans], DATESBETWEEN('REPORTING PERIOD'[ID], firstDatePrevYear,IF(ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
BUD R12 Trans	Y	Y		SUM	N	CALCULATE([BUD Trans], DATESBETWEEN('REPORTING PERIOD'[ID], FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11,month)), LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0,month)) ) )
CFC R12 Trans	Y	Y		SUM	N	CALCULATE([CFC Trans], DATESBETWEEN('REPORTING PERIOD'[ID], FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11,month)), LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0,month)) ) )
NFC R12 Trans	Y	Y		SUM	N	CALCULATE([NFC Trans], DATESBETWEEN('REPORTING PERIOD'[ID], FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11,month)), LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0,month)) ) )
ACT YTD Trans	Y	Y		SUM	N	CALCULATE([ACT Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[ACT_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID]))
ACT Credit YTD Trans	Y	Y		SUM	N	CALCULATE([ACT Credit Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID]))
ACT Debit YTD Trans	Y	Y		SUM	N	CALCULATE([ACT Debit Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID]))
CFC YTD Trans	Y	Y		SUM	N	CALCULATE([CFC Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[CFC_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID]))
NFC YTD Trans	Y	Y		SUM	N	CALCULATE([NFC Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[NFC_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID]))
BUD YTD Trans	Y	Y		SUM	N	CALCULATE([BUD Trans], DATESYTD('REPORTING PERIOD'[ID])) + CALCULATE(SUM('GL ANALYSIS'[BUD_OB_PERIOD_CURR]), DATESYTD('REPORTING PERIOD'[ID]))
ACT XR1 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(), "", [ACT YTD XR1 (YTD Based)] - [ACT OB XR1 (YTD Based)] )
ACT XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([BUD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([CFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([NFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT Debit XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Debit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT Credit XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Credit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT Debit XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Debit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT Credit XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT Credit]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([BUD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([CFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([NFC]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT R12 XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD R12 XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([BUD R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC R12 XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([CFC R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC R12 XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([NFC R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT PY XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT PY]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD PY XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([BUD PY]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD PY R12 XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([BUD PY R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT PY R12 XR1 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT PY R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT OB XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD OB XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([BUD OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC OB XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([CFC OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC OB XR1 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([NFC OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT OB XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD OB XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([BUD OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC OB XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([CFC OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC OB XR2 (Period Based)	Y	N		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([NFC OB]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT PY XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT PY]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD PY XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([BUD PY]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT PY R12 XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT PY R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD PY R12 XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([BUD PY R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT R12 XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD R12 XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([BUD R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC R12 XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([CFC R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC R12 XR2 (Period Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([NFC R12]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACTOBPeriodZero	Y	Y		SUM	N	//Calulcates the incoming balance related to [ACT], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [ACT OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) )
ACTOBPeriodZeroXR1	Y	Y		SUM	N	// Calulates the OB in acc period 0 (as defined in the transactional source) into the selected XRate1 currency using the rate as // defined for the last date in the previous reporting yser. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY'[Currency Code] ) ,"@GLAmount", CALCULATE([ACTOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
ACT YTD XR1 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on ACTuals based on Cross Rate 1 table according to the formula: // 'opening balance period zero using rate XR1 last date prev year' + Sum of ACT value * XR1 for each period up to current context period) [ACTOBPeriodZeroXR1] + CALCULATE([ACT XR1 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
ACT YTD XR1 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([ACT YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT OB XR1 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([ACT OB XR1 (Period Based)]), CALCULATE([ACT YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
BUDOBPeriodZero	Y	Y		SUM	N	//Calulcates the incoming balance related to [BUD], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [BUD OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) )
CFCOBPeriodZero	Y	Y		SUM	N	//Calulcates the incoming balance related to [CFC], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [CFC OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) )
NFCOBPeriodZero	Y	Y		SUM	N	//Calulcates the incoming balance related to [NFC], acc period 0, as defined in the underlying data source // The idea is to calculate the same value for all periods VAR UntilDate = MAX('REPORTING PERIOD'[Period Until Date]) VAR LastDatePY = MAX('REPORTING PERIOD'[Max Reporting Date PY]) VAR DiffInDays = DATEDIFF(LastDatePY, UntilDate, DAY) RETURN CALCULATE ( // go back to last data in previous reporting year + 1 [NFC OB], DATEADD('REPORTING PERIOD'[ID],-DiffInDays+1, DAY) )
BUDOBPeriodZeroXR1	Y	Y		SUM	N	// Calulates the OB in acc period 0 (as defined in the transactional source) into the selected XRate1 currency using the rate as // defined for the last date in the previous reporting yser. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY'[Currency Code] ) ,"@GLAmount", CALCULATE([BUDOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
CFCOBPeriodZeroXR1	Y	Y		SUM	N	// Calulates the OB in acc period 0 (as defined in the transactional source) into the selected XRate1 currency using the rate as // defined for the last date in the previous reporting yser. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY'[Currency Code] ) ,"@GLAmount", CALCULATE([CFCOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
NFCOBPeriodZeroXR1	Y	Y		SUM	N	// Calulates the OB in acc period 0 (as defined in the transactional source) into the selected XRate1 currency using the rate as // defined for the last date in the previous reporting yser. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY'[Currency Code] ) ,"@GLAmount", CALCULATE([NFCOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 1'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
BUD YTD XR1 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on BUD based on Cross Rate 1 table according to the formula: // 'opening balance period zero using rate XR1 last date prev year' + Sum of BUD value * XR1 for each period up to current context period) [BUDOBPeriodZeroXR1] + CALCULATE([BUD XR1 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
CFC YTD XR1 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on CFC based on Cross Rate 1 table according to the formula: // 'opening balance period zero using rate XR1 last date prev year' + Sum of CFC value * XR1 for each period up to current context period) [CFCOBPeriodZeroXR1] + CALCULATE([CFC XR1 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
NFC YTD XR1 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on NFC based on Cross Rate 1 table according to the formula: // 'opening balance period zero using rate XR1 last date prev year' + Sum of NFC value * XR1 for each period up to current context period) [CFCOBPeriodZeroXR1] + CALCULATE([NFC XR1 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
BUD YTD XR1 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([BUD YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC YTD XR1 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([CFC YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC YTD XR1 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 1'[Currency Code X-Rate 1]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY'[Currency Code]) ,"@GLAmount",CALCULATE([NFC YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 1'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD OB XR1 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([BUD OB XR1 (Period Based)]), CALCULATE([BUD YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
CFC OB XR1 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([CFC OB XR1 (Period Based)]), CALCULATE([CFC YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
NFC OB XR1 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([NFC OB XR1 (Period Based)]), CALCULATE([NFC YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
BUD XR1 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(),"", [BUD YTD XR1 (YTD Based)] - [BUD OB XR1 (YTD Based)] )
CFC XR1 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(),"", [CFC YTD XR1 (YTD Based)] - [CFC OB XR1 (YTD Based)] )
NFC XR1 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(),"", [NFC YTD XR1 (YTD Based)] - [NFC OB XR1 (YTD Based)] )
ACT R12 XR1 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [ACT XR1 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
BUD R12 XR1 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [BUD XR1 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
CFC R12 XR1 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [CFC XR1 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
NFC R12 XR1 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [NFC XR1 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
ACT PY XR1 (YTD Based)	Y	Y		SUM	N	CALCULATE([ACT XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY XR1 (YTD Based)	Y	Y		SUM	N	CALCULATE([BUD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACT PY YTD XR1 (YTD Based)	Y	Y		SUM	N	CALCULATE([ACT YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY YTD XR1 (YTD Based)	Y	Y		SUM	N	CALCULATE([BUD YTD XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACT PY R12 XR1 (YTD Based)	Y	Y		SUM	N	CALCULATE([ACT R12 XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY R12 XR1 (YTD Based)	Y	Y		SUM	N	CALCULATE([BUD R12 XR1 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACTOBPeriodZeroXR2	Y	Y		SUM	N	// Calculates the OB in acc period 0 (as defined in the transactional source) into the selected XRate2 currency using the rate as // defined for the last date in the previous reporting user. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY 2'[Currency Code] ) ,"@GLAmount", CALCULATE([ACTOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
BUDOBPeriodZeroXR2	Y	Y		SUM	N	// Calculates the OB in acc period 0 (as defined in the transactional source) into the selected XRate2 currency using the rate as // defined for the last date in the previous reporting user. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY 2'[Currency Code] ) ,"@GLAmount", CALCULATE([BUDOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
CFCOBPeriodZeroXR2	Y	Y		SUM	N	// Calculates the OB in acc period 0 (as defined in the transactional source) into the selected XRate2 currency using the rate as // defined for the last date in the previous reporting user. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY 2'[Currency Code] ) ,"@GLAmount", CALCULATE([CFCOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
NFCOBPeriodZeroXR2	Y	Y		SUM	N	// Calculates the OB in acc period 0 (as defined in the transactional source) into the selected XRate2 currency using the rate as // defined for the last date in the previous reporting user. Should be the same value for any reporting period IF( HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE ( 'GL ANALYSIS' , 'REPORTING PERIOD'[ID], 'REPORTING FROM CURRENCY 2'[Currency Code] ) ,"@GLAmount", CALCULATE([NFCOBPeriodZero]) ,"@XRATE", CALCULATE(SELECTEDVALUE('X-RATES 2'[PREV_YEAR_LAST_PERIOD_RATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRATE]) RETURN XAmount )
ACT YTD XR2 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on ACTuals based on Cross Rate 2 table according to the formula: // 'opening balance period zero using rate XR2 last date prev year' + Sum of ACT value * XR2 for each period up to current context period) [ACTOBPeriodZeroXR2] + CALCULATE([ACT XR2 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
BUD YTD XR2 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on BUD based on Cross Rate 2 table according to the formula: // 'opening balance period zero using rate XR2 last date prev year' + Sum of BUD value * XR2 for each period up to current context period) [BUDOBPeriodZeroXR2] + CALCULATE([BUD XR2 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
CFC YTD XR2 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on CFC based on Cross Rate 2 table according to the formula: // 'opening balance period zero using rate XR2 last date prev year' + Sum of CFC value * XR2 for each period up to current context period) [CFCOBPeriodZeroXR2] + CALCULATE([CFC XR2 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
NFC YTD XR2 (Period Based)	Y	Y		SUM	N	// calulcates the YTD value based on NFC based on Cross Rate 2 table according to the formula: // 'opening balance period zero using rate XR2 last date prev year' + Sum of NFC value * XR2 for each period up to current context period) [NFCOBPeriodZeroXR2] + CALCULATE([NFC XR2 (Period Based)], DATESYTD('REPORTING PERIOD'[ID]) )
ACT YTD XR2 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([ACT YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
BUD YTD XR2 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([BUD YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
CFC YTD XR2 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([CFC YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
NFC YTD XR2 (YTD Based)	Y	Y		SUM	N	IF(HASONEVALUE('CURRENCY CODE X-RATE 2'[Currency Code X-Rate 2]), VAR RestatedAmount = ADDCOLUMNS ( SUMMARIZE('GL ANALYSIS' ,'REPORTING PERIOD'[ID],'REPORTING FROM CURRENCY 2'[Currency Code]) ,"@GLAmount",CALCULATE([NFC YTD]) ,"@XRates" ,CALCULATE(SELECTEDVALUE('X-RATES 2'[CURRENCYRATE])) ) VAR XAmount = SUMX(RestatedAmount, [@GLAmount] * [@XRates]) RETURN XAmount )
ACT OB XR2 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([ACT OB XR2 (Period Based)]), CALCULATE([ACT YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
BUD OB XR2 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([BUD OB XR2 (Period Based)]), CALCULATE([BUD YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
CFC OB XR2 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([CFC OB XR2 (Period Based)]), CALCULATE([CFC YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
NFC OB XR2 (YTD Based)	Y	Y		SUM	N	VAR CurrPeriod = MAX('REPORTING PERIOD'[Reporting Period]) VAR MonthsInPeriod = MAX('REPORTING PERIOD'[Months In Period]) RETURN IF ( CurrPeriod = 1, CALCULATE([NFC OB XR2 (Period Based)]), CALCULATE([NFC YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID], -MonthsInPeriod, MONTH) ) )
ACT XR2 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(),"", [ACT YTD XR2 (YTD Based)] - [ACT OB XR2 (YTD Based)] )
BUD XR2 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(),"", [BUD YTD XR2 (YTD Based)] - [BUD OB XR2 (YTD Based)] )
CFC XR2 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(),"", [CFC YTD XR2 (YTD Based)] - [CFC OB XR2 (YTD Based)] )
NFC XR2 (YTD Based)	Y	Y		SUM	N	IF ( 'GL ANALYSIS MEASURES'[HasOneValuePeriod] = FALSE(),"", [NFC YTD XR2 (YTD Based)] - [NFC OB XR2 (YTD Based)] )
ACT PY XR2 (YTD Based)	Y	Y		SUM	N	CALCULATE([ACT XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY XR2 (YTD Based)	Y	Y		SUM	N	CALCULATE([BUD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACT R12 XR2 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [ACT XR2 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
BUD R12 XR2 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [BUD XR2 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
CFC R12 XR2 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [CFC XR2 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
NFC R12 XR2 (YTD Based)	Y	Y		SUM	N	VAR lastDatePrevMonth = LASTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],0 , month)) VAR firstDatePrevYear = FIRSTDATE(PARALLELPERIOD('REPORTING PERIOD'[ID],-11 , month)) RETURN CALCULATE ( [NFC XR2 (YTD Based)], DATESBETWEEN ( 'REPORTING PERIOD'[ID], firstDatePrevYear, IF ( ISBLANK(lastDatePrevMonth), CALCULATE(MIN('REPORTING PERIOD'[ID]), ALL('REPORTING PERIOD')), lastDatePrevMonth ) ) )
ACT PY R12 XR2 (YTD Based)	Y	Y		SUM	N	CALCULATE([ACT R12 XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY R12 XR2 (YTD Based)	Y	Y		SUM	N	CALCULATE([BUD R12 XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
ACT PY YTD XR2 (YTD Based)	Y	Y		SUM	N	CALCULATE([ACT YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
BUD PY YTD XR2 (YTD Based)	Y	Y		SUM	N	CALCULATE([BUD YTD XR2 (YTD Based)], DATEADD('REPORTING PERIOD'[ID],-12,month))
HasOneValuePeriod	Y	N		N/A	Y	HASONEVALUE('REPORTING PERIOD'[Reporting Period])

Time Intelligence Measures

Time based measures are calculated in the cube with respect to other measures. The table below defines the meaning/definition of the different time measures. These measures need to be used with a specific time dimension hierarchy [REPORTING_DATE].[RepDateMFHy].

Measure	Meaning
YTD	Aggregated value for current month in current year. E.g. for period 202010 Ytd represents sum of periods 202000-202010.
R12	For a given period, the sum all months from one year back up to the previous month. E.g. Rolling 12 for 202010 is the sum of months 201910 - 202009
PY	Previous Year The value of the corresponding month one year back. E.g. for month 202010, the value of month 201910.
YTD PY	Year to Date Previous Year Aggregated value for corresponding month one year back. E.g. for month 202010 YTD Previous Year represents sum of months 201901-201910.
R12 PY	Rolling 12 (completed) Months Previous Year For a given month, first going one year back, then taking the sum of all months from one (more) year back up to the previous month. E.g. for month 202010 the sum of month 201810-201909
Avg XXX Ytd	Sum XXX YTD/Count XXX YTD
Avg XXX R12	Sum XXX R12/Count XXX R12
XXX % YTD	Count XXX YTD/Count YYY YTD
XXX % R12	Count XXX R12/Count XXX R12

USED BY MODEL(S)

Model Name	Product Area
General Ledger	Financials

Data source Information

The data source for this object is defined using IFS Developer Tool. The data source contains a number of transformations where the model reads data from the last step, i.e. the Data source view name (DW Source View Name).

Additional information can be found in the online documentation of Information Sources.

Model Table	DW Source view (SQL Server)	BI Access View	Referenced Information Source	Recommended Access Type
GL ANALYSIS MEASURES	FACT_TRANS_BAL_SET_AM_CONF_TM	FACT_GL_TRANSACTION_BI, FACT_GL_BALANCE_BI, FACT_GL_PERIOD_BUDGET_BI, FACT_PLAN_TRANS_VALID_BI	FACT_GL_TRANSACTION, FACT_GL_BALANCE, FACT_GL_PERIOD_BUDGET, FACT_PLAN_TRANS_VALID	Data Mart